1. Field of the Invention
Embodiments of the invention are generally related to computer database systems. More particularly, embodiments of the invention are related to evaluating a database query and selecting a database locking strategy prior to query execution.
2. Description of the Related Art
Databases are computerized information storage and retrieval systems. A relational database management system (DBMS) is a computer database management system that uses relational techniques for storing and retrieving data. Relational databases are computerized information storage and retrieval systems in which data in the form of tables are typically stored for use on disk drives or similar mass data stores. Each database table includes a set of rows (also referred to as records) spanning one or more columns.
A database query refers to a set of commands or clauses for retrieving data stored in a database. Database queries may come from users, application programs or remote systems. A query may specify which columns to retrieve data from, how to join columns from multiple tables, and conditions that must be satisfied for a particular data record to be included in a query result set. Current relational databases typically process queries composed in an exacting format specified by a query language. For example, the widely used query language SQL (short for Structured Query Language) is supported by virtually every database available today.
Internally, a database may store the records of a given table in one or more pages. For example, the DB2® database available from IBM is configured to store records using a page size of 4096 bytes. The size of a page may be adjusted for performance considerations. Each page may store a number of records for the given table. When database queries are executed, rows from a variety of pages may be accessed. If multiple users have access to the database, i.e., if multiple queries may be executed concurrently, then the actions of one query (e.g., a query writing to rows on a particular page), may disrupt the actions of another query (e.g., another query reading rows from that page). Accordingly, a DBMS will “lock” the records accessed as part of a given database query. While locked, only the query which “locked” a particular record (or page) may access that record (or page). A row level lock “locks” a single record. A page level lock “locks” each record on the page, and a table level lock “locks” each page with any records from the “locked” table.
Locking strategy may dramatically affect performance. For example, in an environment with many users, using row level locking is more advantageous as it only locks the record being requested, other records on the same page remain available to other queries being processed. However, page level locking is often superior, as it requires fewer locks and fewer internal structures to be created to manage the locks, and thus, allows queries to be executed more efficiently and quickly. Situations where page level locking is more advantageous are situations where the likelihood of locking “extra” rows will unlikely cause deadlock (i.e., two locks, each waiting for the other to release) or concurrency problems (i.e., a query being blocked from access to a page until the page is unlocked).
One common locking strategy used by DBMS is known as lock escalation. Lock escalation is the process of converting many fine-grain (i.e., row level) locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of deadlock and concurrency contention. Lock escalation is done during execution time and certain thresholds must be reached before lock escalation is done. For example, in SQL Server, lock escalation is triggered:                when a single Transact-SQL statement acquires at least 5,000 locks on a single table or index; or        when the number of locks in an instance of the DBMS exceeds memory or configuration thresholds.If locks cannot be escalated because of lock conflicts, the DBMS periodically attempts to trigger lock escalation after 1,250 new locks are acquired. Other DBMS systems provide similar features.        
One drawback to this approach, however, is that escalation occurs only during query execution, and only after a problem with row level locking is identified. Essentially this approach waits until performance is degraded before escalating to a page (or table) level locking strategy. By this time however, the DBMS is at best playing catch up once query execution is going poorly.
Accordingly, there is a need in the art for a database query optimization technique for selecting a locking strategy, prior to executing a database query.